package com.matt.repository;

import com.matt.domain.WorkDate;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2017/7/21
 * *******************************
 */
public interface WorkDateRepository extends JpaRepository<WorkDate, String> {

    WorkDate findByWorkDate(Date workDate);

    @Query(value = "SELECT COUNT(id) FROM work_date WHERE workDate >= :beginDate  and  workDate <= :endDate", nativeQuery = true)
    Integer findByWorkDateDay(@Param("beginDate")Date beginDate,@Param("endDate") Date endDate);


    @Query(value = "SELECT * FROM work_date WHERE  if(:beginDate != '', workDate >= :beginDate, 1=1)  and  if(:endDate != '', workDate < :endDate, 1=1)  ", nativeQuery = true)
    List<WorkDate> findByWorkDateDayList(@Param("beginDate")Date beginDate, @Param("endDate") Date endDate);

    long countAllByWorkDateBetween(Date beginDate, Date endDate);


    /**
     * 日期顺延
     * @param workDate
     * @param day
     * @return
     */
    @Query(value = "SELECT * FROM work_date WHERE  workDate > :workDate  ORDER BY workDate  LIMIT :day ,1", nativeQuery = true)
    WorkDate getWorkDateAscDay(@Param("workDate")Date workDate,@Param("day") Integer day);

    /**
     * 入户扣课
     * @param workDate
     * @param day
     * @return
     */
    @Query(value = "SELECT * FROM work_date WHERE  workDate < :workDate  ORDER BY workDate desc  LIMIT :day ,1", nativeQuery = true)
    WorkDate getWorkDateDescDay(@Param("workDate")Date workDate,@Param("day") Integer day);

    @Query(value = "SELECT * FROM work_date  ORDER BY workDate desc  ", nativeQuery = true)
    List<WorkDate> getOrderByWorkDateDesc();
}
